import sqlite3


class DBOperate:
    def __init__(self, dbName):
        self.__connect=sqlite3.connect(database=dbName)

    def Query(self, sql:str)->list:
        """"""
        queryResult = self.__connect.cursor().execute(sql).fetchall()
        # self.CloseDB()
        return list(queryResult)

    def QueryAsDict(self, sql:str):
        """调用该函数返回结果为字典形式"""
        self.__connect.row_factory=self.dictFactory
        cur=self.__connect.cursor()
        queryResult=cur.execute(sql).fetchall()
        # self.CloseDB()
        return queryResult

    def Insert(self,sql:str):

        self.__connect.cursor().execute(sql)
        self.__connect.commit()
        # self.CloseDB()
        return True

    def InsertBook(self, table, data:list):
        self.__connect.cursor().\
            executemany(f"INSERT INTO {table} (WORD, NATURE, CHINESE) VALUES  (?, ?, ?)",
                        data)
        # self.__connect.commit()
        return True

    def Update(self,sql:str):
        self.__connect.cursor().execute(sql)
        self.__connect.commit()
        # self.CloseDB()
        return True

    def Delete(self,sql:str):
        self.__connect.cursor().execute(sql)
        self.__connect.commit()
        # self.CloseDB()
        return True

    def dictFactory(self,cursor,row):
        """将sql查询结果整理成字典形式"""
        d={}
        for index,col in enumerate(cursor.description):
            d[col[0]]=row[index]
        return d

    def defaultSQL(self, sql:str):
        return self.__connect.cursor().execute(sql).fetchall()

    def CreateTable(self, table:str):
        sql = f"CREATE TABLE {table}" \
                  "(WORD CHAR(50) PRIMARY KEY NOT NULL," \
                  "NATURE CHAR(20)," \
                  "CHINESE CHAR(50) NOT NULL);"
        self.__connect.cursor().execute(sql)
        self.__connect.commit()

    def CloseDB(self):
        self.__connect.cursor().close()
        self.__connect.close()
        return True